package Mapper;

import Utils.JdbcUtils;
import Utils.ThreadLocalUtis;
import pojo.Bill;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDate;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.util.*;

public class OutputBillMapper {
    private JdbcUtils jdbcUtils = new JdbcUtils();

    public int outputBill(Bill bill) throws SQLException {
        String sql = "insert into outputbill (billid, billname, money, date, billtype, comment, userid) VALUES (?,?,?,?,?,?,?)";
        jdbcUtils.getPrepareStatement(sql);
        PreparedStatement prepareStatement = jdbcUtils.getPrepareStatement(sql);
        prepareStatement.setInt(1, bill.getBillId());
        prepareStatement.setString(2, bill.getBillName());
        prepareStatement.setDouble(3, bill.getMoney());
        prepareStatement.setObject(4, bill.getDate());
        prepareStatement.setString(5, bill.getBillType());
        prepareStatement.setString(6, bill.getComment());
        Map<String, Object> map = ThreadLocalUtis.get();
        int userid = (Integer) map.get("userid");
        prepareStatement.setInt(7, userid);
        int i = prepareStatement.executeUpdate();
        return i;
    }

    public List<Bill> getOutputBills() {
        Map<String, Object> map = ThreadLocalUtis.get();
        int userid = (Integer) map.get("userid");
        String sql = "select * from outputbill where userid = ?";
        List<Bill> bills = null;
        try {
            PreparedStatement prepareStatement = jdbcUtils.getPrepareStatement(sql);
            prepareStatement.setInt(1, userid);
            ResultSet resultSet = prepareStatement.executeQuery();
            bills = new ArrayList<>();
            while (resultSet.next()) {
                int billId = resultSet.getInt(1);
                String billName = resultSet.getString(2);
                double money = resultSet.getDouble(3);
                Date date =  resultSet.getDate(4);
                String billType = resultSet.getString(5);
                String comment = resultSet.getString(6);
                Bill bill = new Bill(billId, billName, money, date, billType, comment, userid);
                bills.add(bill);
            }

        } catch (SQLException e) {
            e.getStackTrace();
        }
        return bills;

    }

    public static void main(String[] args) {
        Map<String, Object> map = new HashMap<>();
        map.put("userid", 20231009);
        ThreadLocalUtis.set(map);
        OutputBillMapper outputBillMapper = new OutputBillMapper();
        List<Bill> outputBills = outputBillMapper.getOutputBills();
        for (int i = 0; i < outputBills.size(); i++) {
            System.out.println(outputBills.get(i));
        }
    }

    public List<Bill> getInputBillForTime(Date formatDate) {
        String sql = "select * from  outputbill where date   = ? and userid = ?";
        Map<String, Object> map = ThreadLocalUtis.get();
        int userid =(Integer) map.get("userid");
        List<Bill> bills = null;
        try {
            PreparedStatement prepareStatement = jdbcUtils.getPrepareStatement(sql);
            java.sql.Date sqlDate = new java.sql.Date(formatDate.getTime());
            prepareStatement.setDate(1, sqlDate);
            prepareStatement.setInt(2, userid);
            ResultSet resultSet = prepareStatement.executeQuery();
            while (resultSet.next()) {
                int billId = resultSet.getInt(1);
                String billName = resultSet.getString(2);
                double money = resultSet.getDouble(3);
                Date localdate = resultSet.getDate(4);
                String billType = resultSet.getString(5);
                String comment = resultSet.getString(6);
                Bill bill = new Bill(billId, billName, money, localdate, billType, comment, userid);
                bills.add(bill);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return bills;

    }
    public List<Bill> getMonthBill(){
        int year = LocalDate.now().getYear();
        int month = LocalDate.now().getMonthValue();
        Map  <String ,  Object> map = ThreadLocalUtis.get();
        int userid =   (Integer) map.get("userid");
        String sql =  "select * from inputbill where  year(date)="+year+" and   month(date)  =  "+month+"";
        List<Bill> bills = null;
        try {
            Statement statement = jdbcUtils.getStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            bills = new ArrayList<>();
            while (resultSet.next()){
                int billId = resultSet.getInt(1);
                String billName = resultSet.getString(2);
                double money = resultSet.getDouble(3);
                Date localdate = resultSet.getDate(4);
                String billType = resultSet.getString(5);
                String comment = resultSet.getString(6);
                Bill bill = new Bill(billId, billName, money, localdate, billType, comment, userid);
                bills.add(bill);
            }
        } catch (SQLException e) {
            try {
                jdbcUtils.getConn().rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
        return  bills;
    }public List<Bill> getMonthBill(int year, int month){
        Map  <String ,  Object> map = ThreadLocalUtis.get();
        int userid =   (Integer) map.get("userid");
        String sql =  "select * from inputbill where  year(date)="+year+" and   month(date)  =  "+month+"";
        List<Bill> bills = null;
        try {
            Statement statement = jdbcUtils.getStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            bills = new ArrayList<>();
            while (resultSet.next()){
                int billId = resultSet.getInt(1);
                String billName = resultSet.getString(2);
                double money = resultSet.getDouble(3);
                Date localdate = resultSet.getDate(4);
                String billType = resultSet.getString(5);
                String comment = resultSet.getString(6);
                Bill bill = new Bill(billId, billName, money, localdate, billType, comment, userid);
                bills.add(bill);
            }
        } catch (SQLException e) {
            try {
                jdbcUtils.getConn().rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
        return  bills;
    }
}
